﻿CREATE PROCEDURE [dbo].[proc_Statistics_Financial_Tongji_Create_BYCompanyId_Tool]
	(
		@idate datetime,
		@CompanyId INT,
		@CompanyName nvarchar(50)
	)
AS
Begin

	
--if CONVERT(varchar(100), GETDATE(), 8)<'16:30:00'
--return 

--账目平整可调整应有现金和应有银行。资金合计=应有现金+应有银行
	--支出变量
	Declare @OAA decimal(18,2) ,@OAB decimal(18,2) ,@OBA decimal(18,2) ,@OBB decimal(18,2) ,@OBC decimal(18,2) ,@OBD decimal(18,2) ,@OBE decimal(18,2) ,@OBF decimal(18,2) ,@OBG decimal(18,2) ,@OBH decimal(18,2) ,@OCA decimal(18,2) ,@OCB decimal(18,2) , @OCC decimal(18,2) ,@ODA decimal(18,2) ,@ODB decimal(18,2)
	DECLARE @OEA decimal(18,2) ,@OEB decimal(18,2) ,@OEC decimal(18,2) ,@OED decimal(18,2) ,@OEE decimal(18,2) ,@OEF decimal(18,2) ,@OEG decimal(18,2) ,@OEH decimal(18,2) ,@OEI decimal(18,2) ,@OEJ decimal(18,2) ,@OEK decimal(18,2) ,@OEL decimal(18,2) ,@OEM decimal(18,2) ,@OEN decimal(18,2) ,@OEO decimal(18,2) ,@OEP decimal(18,2) ,@OEQ decimal(18,2)
	DECLARE @OER decimal(18,2) ,@OES decimal(18,2) 
	
	--业绩变量 
	Declare @IAA decimal(18,2) ,@IAB decimal(18,2) ,@IAC decimal(18,2) ,@IAD decimal(18,2) ,@IAE decimal(18,2) ,@IAF decimal(18,2) ,@IAG decimal(18,2) ,@qqqdfqzcxse DECIMAL(18,2),@qqqdfqzcml decimal(18,2) ,@qqqd decimal(18,2) 
	
	--资金变量
	Declare @IBA decimal(18,2) ,@IBB decimal(18,2) ,@IBC decimal(18,2) ,@IBD decimal(18,2) ,@IBDA decimal(18,2) ,@IBDB decimal(18,2) ,@IBE decimal(18,2) ,@IBEA decimal(18,2) ,@IBEB decimal(18,2) ,
			@IBF decimal(18,2) ,@IBG decimal(18,2) ,@IBH decimal(18,2) ,@IBHA decimal(18,2) ,@IBHB decimal(18,2) ,@IBI decimal(18,2) ,@IBIA decimal(18,2) ,@IBIB decimal(18,2) ,@IBJ decimal(18,2) , @IBK decimal(18,2) ,@IBL decimal(18,2),@jj DECIMAL(18,2),@bcfk DECIMAL(18,2),@kqfk DECIMAL(18,2)
	
	--昨日现金银行
	Declare @YIBD decimal(18,2) ,@YIBE decimal(18,2)
	Declare @xzxse DECIMAL(18,2),@xfxse DECIMAL(18,2)	
	--公司表变量
	Declare @CreateDate varchar(10)	
	Set @CreateDate=convert(varchar(10),@idate,120)
	Declare @sRq Datetime,@eRq Datetime
	Set @sRq = Convert(Datetime, @CreateDate + ' 00:00:00')
	Set @eRq = Convert(Datetime, @CreateDate + ' 23:59:59')
	select @CreateDate,@sRq,@eRq
	set @CreateDate=@CreateDate + ' 23:00:00'
	Declare @start int, @end int,  @today int
	--Select  @end=1--Count(Id) From @indextable
	--Select  @start=1
	--While(@start<=@end)
	--	begin
			
			
			--支出统计
			--Set @OAA=ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=@companyId and Convert(nchar(10),CreateDate,120)=@CreateDate and CateTwoId=20),0)--直接成本
			--直接成本，支出中所有的直接成本，减去存账户余额时产生的支出
			SET @OAA=ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=@companyId and CreateDate>=@sRq and CreateDate<=@eRq and CateTwoId=20),0)
			                  -isnull((Select sum(je) From [AB_List] Where CompanyId =@companyId And rootId=2 And AB_Type=1 And Convert(varchar(10),UpTime,120)=@CreateDate),0)--直接成本
			               
			Set @OAB=ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=@companyId and CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=21),0)--间接成本
			Set @OBA=ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=@companyId and CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=8),0) +
					ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1 and CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=8),0)---市场费用
			Set @OBB=ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=@companyId and CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=1),0) +
					ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1 and CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=1),0)---日常生活
			Set @OBC=ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=@companyId and CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=5),0) +
					ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1 and CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=5),0)---行政办公
			Set @OBD=ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=@companyId and CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=9),0) +
					ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1 and CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=9),0)---财务费用
			Set @OBE=ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=@companyId and CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=10),0) +
					ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1 and CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=10),0)--人力费用
			Set @OBF=ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=@companyId and CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=11),0) +
					ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1 and CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=11),0)--部门基金
			Set @OBG=ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=@companyId and CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=7),0) +
					ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1 and CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=7),0)---总办费用
			Set @OBH=ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=@companyId and CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=12),0) +
					ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1 and CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=12),0)--其他费用
			Set @OCA=ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=@companyId and CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=25),0) +
					ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1 and CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=25),0)--投资
			Set @OCB=ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=@companyId and CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=18),0) +
					ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1 and CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=18),0)--借款
			Set @OCC=ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=@companyId and CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=26),0) +
					ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1 and CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=26),0)--往来借款
			                                                                                                                                                                 --
			Set @ODA=ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=@companyId and CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=23),0) +
					ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1 and CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=23),0)--有形资产
			Set @ODB=ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=@companyId and CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=24),0) +
					ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1 and CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=24),0)--无形资产
			
			
			
			-----
			Set @OEA=ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=@companyId and  CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=27),0) +
					ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1 and CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=27),0)--员工工资

			Set @OEB=ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=@companyId and  CreateDate>=@sRq and CreateDate<=@eRq  and SubjectId=212),0) +
					ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1 and CreateDate>=@sRq and CreateDate<=@eRq  and SubjectId=212),0)---保险

			Set @OEC=ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=@companyId and  CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=29),0) +
					ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1 and CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=29),0)---税金
			Set @OED=ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=@companyId and  CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=30),0) +
					ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1 and CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=30),0)---房费
			Set @OEE=ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=@companyId and  CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=31),0) +
					ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1 and CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=31),0)--电话宽带费
			Set @OEF=ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=@companyId and  CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=32),0) +
					ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1 and CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=32),0)--水电费
			Set @OEG=ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=@companyId and  CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=33),0) +
					ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1 and CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=33),0)---办公费用
			Set @OEH=ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=@companyId and  CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=34),0) +
					ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1 and CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=34),0)--车辆费
			Set @OEI=ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=@companyId and  CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=35),0) +
					ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1 and CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=35),0)--差旅费
			Set @OEJ=ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=@companyId and  CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=36),0) +
					ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1 and CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=36),0)--招待费
			Set @OEK=ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=@companyId and  CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=37),0) +
					ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1 and CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=37),0)--财务费用
			Set @OEL=ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=@companyId and  CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=38),0) +
					ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1 and CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=38),0)---招聘费
			Set @OEM=ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=@companyId and  CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=39),0) +
					ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1 and CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=39),0)---装修费
			Set @OEN=ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=@companyId and  CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=40),0) +
					ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1 and CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=40),0)--市场宣传费
			Set @OEO=ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=@companyId and  CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=41),0) +
					ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1 and CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=41),0)--员工福利
			Set @OEP=ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=@companyId and  CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=42),0) +
					ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1 and CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=42),0)--基金支出
			Set @OEQ=ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=@companyId and  CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=12),0) +
					ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1 and CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=12),0)--其他费用

			Set @OER=ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=@companyId and  CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=44),0) +
					ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1 and CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=44),0)--上缴资金
			Set @OES=ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=@companyId and  CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=45),0) +
					ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1 and CreateDate>=@sRq and CreateDate<=@eRq  and CateTwoId=45),0)--其他成本支出

			
			------------------
						
			
			--业绩统计
			Set @IAA=ISNULL((Select Sum(SalePrice-CostPrice) From Project Where CompanyId=@companyId and  CreateDate>=@sRq and CreateDate<=@eRq  and TypeId=1 AND ProductId not IN (22,29,33) and SalePrice<>CostPrice  AND Project.NOE_Flag=0 ),0)+
			ISNULL((SELECT sum(dch_xzml) FROM Order_Change_Hedge inner JOIN [Order] o on Order_Change_Hedge.oid = o.oid and Order_Change_Hedge.CompanyId=@companyId and UpTime>=@sRq and UpTime<=@eRq  and o.companyId=@companyId AND o.NOE_Flag=0 ),0)--新增毛利AND Project.NOE_Flag<>2|and oid IN (SELECT oid FROM [Order] o WHERE companyId=@companyId AND o.NOE_Flag<>2 )
			
			Set @IAB=ISNULL((Select Sum(SalePrice-CostPrice) From Project Where CompanyId=@companyId and  CreateDate>=@sRq and CreateDate<=@eRq  and TypeId=2 AND ProductId not IN (22,29,33) and SalePrice<>CostPrice  AND Project.NOE_Flag=0 ),0)+
					ISNULL((SELECT sum(dch_xfml) FROM Order_Change_Hedge inner join [Order] o on Order_Change_Hedge.oid =o.oid and Order_Change_Hedge.CompanyId=@companyId and UpTime>=@sRq and UpTime<=@eRq  and o.companyId=@companyId AND o.NOE_Flag=0),0)--续费毛利
			
			--YingShouPrice-ShiShouPrice 2014-12-30取消使用,用发生额h_yingshou代替 2015.3.9 按财务认领时间统计+ 2016-03-05 包含非常规绿色通道
			Set @IAC=ISNULL((Select Sum(h_yingshou) From [Order] WHERE CompanyId=@CompanyId and financerenlingdate>=@sRq and financerenlingdate<=@eRq AND StateId>=1  AND NOE_Flag<>2 ),0)+
			ISNULL((SELECT sum(dch_ysh) FROM Order_Change_Hedge inner join [Order] o on Order_Change_Hedge.oid =o.oid  and Order_Change_Hedge.CompanyId=@companyId and UpTime>=@sRq and UpTime<=@eRq  and o.companyId=@companyId AND o.NOE_Flag<>2 ),0)+
			ISNULL((Select Sum(h_yingshou) From [Order] WHERE CompanyId=@CompanyId and financerenlingdate>=@sRq and financerenlingdate<=@eRq AND StateId>=1  AND NOE_Flag=2 And IsGreen=1),0)
			--新增应收
			
			--2016-03-04 当天应付，包含以后的付款
			Set @IAD=ISNULL((Select Sum(CostPrice) From Project Where CompanyId =@CompanyId and FinanceAudit=1 and  CreateDate>=@sRq and CreateDate<=@eRq ),0)+
			ISNULL((SELECT sum(dch_yf) FROM Order_Change_Hedge WHERE CompanyId=@companyId and UpTime>=@sRq and UpTime<=@eRq  ),0)   --新增应付
			
			DECLARE @IADQT decimal(18,2)
			Set @IADQT=ISNULL((Select Sum(CostPrice) From Project Where CompanyId =@CompanyId and FinanceAudit=2 
			and  CreateDate>=@sRq and CreateDate<=@eRq and FinanceAuditDate>@eRq ),0)
			Set @IAD=@IAD+@IADQT

		    Set @IAE=ISNULL((Select Sum(F_YingShou) From [Order] Where CompanyId=@CompanyId AND StateId>=1 and financerenlingdate>=@sRq and financerenlingdate<=@eRq  AND NOE_Flag<>2 ),0)+
		    ISNULL((SELECT sum(dch_chd) FROM Order_Change_Hedge inner join [Order] o on Order_Change_Hedge.oid =o.oid and Order_Change_Hedge.CompanyId=@companyId and Order_Change_Hedge.UpTime>=@sRq and Order_Change_Hedge.UpTime<=@eRq  and o.companyId=@companyId AND o.NOE_Flag<>2 ),0)+
					ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId 
		and TypeId=3  and  CreateDate>=@sRq and CreateDate<=@eRq  and OrderType=2),0)+
				ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId 
		and TypeId=3  and  CreateDate>=@sRq and CreateDate<=@eRq  and OrderType=1),0)--成单金额

			
			Set @IAF=ISNULL((Select Sum(Prices) From Order_PaymentRecord inner JOIN [Order] o on  Order_PaymentRecord.oid = o.oid and Order_PaymentRecord.CompanyId=@companyId and FinanceDate>=@sRq and FinanceDate<=@eRq and SWId=2 and  o.companyId=@companyId AND o.NOE_Flag<>2),0)+
			ISNULL((SELECT sum(dch_wk) FROM Order_Change_Hedge inner JOIN [Order] o on Order_Change_Hedge.oid = o.oid and Order_Change_Hedge.CompanyId=@companyId and UpTime>=@sRq and UpTime<=@eRq and o.companyId=@companyId AND o.NOE_Flag<>2 ),0)--尾款收入
			
			--DECLARE @iaf_a DECIMAL(18,2)
			
			--set @iaf_a=0
			--ISNULL((SELECT SUM(Order_PaymentRecord.prices) FROM Order_PaymentRecord inner JOIN [Order] o on Order_PaymentRecord.oid =o. oid AND  Order_PaymentRecord.CompanyId=@CompanyId 
			--		  AND o.StateId>=1 and o.CompanyId=@CompanyId AND FinanceDate>=@sRq and FinanceDate<=@eRq  and SWId=2   AND o.NOE_Flag<>2 AND o.AuditDate>=@sRq and o.AuditDate<=@eRq ),0)

			--SET @IAF=@IAF-@iaf_a	--当日成单当日收回来的尾款，不参与计算	，算在当日的实收里	2012-10-10
			
			--Set @IAG=ISNULL((SELECT SUM(p.SalePrice-p.CostPrice) FROM Project p WHERE p.ProductId IN (22,29,33) AND p.CompanyId=@CompanyId AND CreateDate>=@sRq and CreateDate<=@eRq   ),0)--非营业性收入
			--2015-1-21非营业性收入从新模块非营业性收入管理中统计 非营业性收入+其他收入 ,2016-03-03修改，包含项目中其他产品分类下的其他
			Set @IAG=ISNULL((SELECT SUM(income) FROM Finance_RunningAccount_Non WHERE  CompanyId=@CompanyId AND CreateDate>=@sRq and CreateDate<=@eRq   ),0) +
					ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=2 and  CreateDate>=@sRq and CreateDate<=@eRq ),0)+
					ISNULL((SELECT SUM(p.SalePrice-p.CostPrice) FROM Project p WHERE p.ProductId=33 AND p.CompanyId=@CompanyId AND CreateDate>=@sRq and CreateDate<=@eRq),0)
					--非营业性收入 
			/****************↓统计成单额↓*****************/
		--新增成单额 2015-03-09 成单额按财务认款时间统计FinanceRenlingDate
		 Set @xzxse=ISNULL((Select Sum(F_YingShou) From [Order] Where CompanyId=@CompanyId and typeid=1 AND StateId>=1 
		 and financerenlingdate>=@sRq and financerenlingdate<=@eRq  AND NOE_Flag<>2 ),0)+
		ISNULL((SELECT sum(dch_chd) FROM Order_Change_Hedge inner join [Order] o on Order_Change_Hedge.oid =o.oid 
		and Order_Change_Hedge.CompanyId=@companyId  and typeid=1 and Order_Change_Hedge.UpTime>=@sRq 
		and Order_Change_Hedge.UpTime<=@eRq  and o.companyId=@companyId AND o.NOE_Flag<>2 ),0)
		+ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId 
		and TypeId=3  and  CreateDate>=@sRq and CreateDate<=@eRq  and OrderType=1),0)
		--成单金额
		 
		 --续费成单额 2015-03-09 成单额按财务认款时间统计
		Set @xfxse=ISNULL((Select Sum(F_YingShou) From [Order] Where CompanyId=@CompanyId and typeid=2 AND StateId>=1 
		 and financerenlingdate>=@sRq and financerenlingdate<=@eRq  AND NOE_Flag<>2 ),0)+
		ISNULL((SELECT sum(dch_chd) FROM Order_Change_Hedge inner join [Order] o on Order_Change_Hedge.oid =o.oid 
		and Order_Change_Hedge.CompanyId=@companyId  and typeid=2 and Order_Change_Hedge.UpTime>=@sRq 
		and Order_Change_Hedge.UpTime<=@eRq  and o.companyId=@companyId AND o.NOE_Flag<>2 ),0)
		+ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId 
		and TypeId=3  and  CreateDate>=@sRq and CreateDate<=@eRq  and OrderType=2),0)
		--成单金额

		--@IAE :成单总额=新增成单额+续费成单额 
		 Set @IAE=@xzxse+@xfxse
		/*********************************/
			
			--资金统计 
			--(1)账目平整------------------------
			
			Set @IBHA=ISNULL((Select Sum(Prices) From Finance_Balance Where CompanyId=@companyId and TypeId=1 and PaymentTypeId=1 and CurrentDate>=@sRq and CurrentDate<=@eRq),0)--当日现金收入平整
			
			Set @IBHB=ISNULL((Select Sum(Prices) From Finance_Balance Where CompanyId=@companyId and TypeId=2 and PaymentTypeId=1 and CurrentDate>=@sRq and CurrentDate<=@eRq),0)--当日现金支出平整
			
			Set @IBH=@IBHA-@IBHB--现金平整
			
			Set @IBIA=ISNULL((Select Sum(Prices) From Finance_Balance Where CompanyId=@companyId and TypeId=1 and PaymentTypeId<>1 and CurrentDate>=@sRq and CurrentDate<=@eRq),0)--当日银行收入平整
			
			Set @IBIB=ISNULL((Select Sum(Prices) From Finance_Balance Where CompanyId=@companyId and TypeId=2 and PaymentTypeId<>1 and CurrentDate>=@sRq and CurrentDate<=@eRq),0)--当日银行支出平整
			
			Set @IBI=@IBIA-@IBIB--银行平整
			
			--(2)应有现金-------------------------
			
			Set @YIBD=ISNULL((Select IBD From Statistics_Financial Where CompanyId=@companyId and CreateDate>=dateadd(d,-1,@sRq) and CreateDate<=dateadd(d,-1,@eRq)),0)--昨日现金合计
			
			Set @IBDA=ISNULL(( SELECT SUM([Income]) FROM [Finance_RunningAccount] WHERE  CompanyId=@CompanyId AND  (PaymentTypeId=1 or BankId IN (SELECT id FROM Finance_Bank fb WHERE fb.PaymentTypeId=1 and Companyid= @CompanyId)) 
 AND CreateDate>=@sRq and CreateDate<=@eRq ),0)--ISNULL((Select Sum(Income) From Finance_RunningAccount Where CompanyId=@companyId and Convert(nchar(10),CreateDate,120)=@CreateDate and PaymentTypeId=1),0)--当日现金收入
			
			Set @IBDB=ISNULL((SELECT sum(outlayprice)
  FROM [Finance_Outlay] 
WHERE  companyidcur=@CompanyId AND [cb_flag]=0 and (PaymentTypeId=1 or BankId IN (SELECT id FROM Finance_Bank fb WHERE fb.PaymentTypeId=1 and Companyid= @CompanyId)) 
  and CreateDate>=@sRq and CreateDate<=@eRq ),0)

--支出审核时生成的成本类支出不在现金银行中减少 ，按财务审核日期
---isnull((SELECT SUM(costprice) FROM Project p WHERE  p.CompanyId=@CompanyId AND p.FinanceAuditDate>=@sRq and p.FinanceAuditDate<=@eRq
--AND p.FinanceAudit=2 AND ((Select PaymentTypeId From [Order] Where [Order].companyid=@CompanyId and OId=p.OrderId)=1 
--   OR (Select PaymentTypeId From [Order] Where OId=p.OrderId and [Order].companyid=@CompanyId AND [Order].BankId IN (SELECT fb.id FROM Finance_Bank fb WHERE fb.companyid=@CompanyId and fb.PaymentTypeId=1) )=2 )),0)
----ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=@companyId and Convert(nchar(10),CreateDate,120)=@CreateDate and PaymentTypeId=1),0)--当日现金支出
			
			Set @IBD=@YIBD+(@IBDA-@IBDB)+@IBH+ISNULL((SELECT sum(dch_xj) FROM Order_Change_Hedge WHERE UpTime>=@sRq and UpTime<=@eRq AND CompanyId=@CompanyId),0)
+
--存现-提现
(isnull((SELECT sum(cx) FROM txcx t WHERE CompanyId=@CompanyId AND  CreateDate>=@sRq and CreateDate<=@eRq AND (t.cx_PaymentTypeId=1 or t.cx_BankId IN (SELECT id FROM Finance_Bank fb WHERE fb.PaymentTypeId=1 and Companyid= @CompanyId)) ),0)
	-
isnull((SELECT sum(tx) FROM txcx t WHERE CompanyId=@CompanyId AND CreateDate>=@sRq and CreateDate<=@eRq AND  (t.tx_PaymentTypeId=1 or t.tx_BankId IN (SELECT id FROM Finance_Bank fb WHERE fb.PaymentTypeId=1 and Companyid= @CompanyId))),0)
)
+
isnull((SELECT SUM([Income])  FROM Finance_RunningAccount_Non WHERE Companyid=@Companyid and
   createdate>=@sRq and createdate<=@eRq 
 AND (PaymentTypeId=1 or BankId IN (SELECT id FROM Finance_Bank fb WHERE Companyid=@Companyid and fb.PaymentTypeId=1 ))),0)
 
--应有现金
			
			--(3)应有银行-------------------------
			
			Set @YIBE=ISNULL((Select IBE From Statistics_Financial Where CompanyId=@companyId and CreateDate>=dateadd(d,-1,@sRq) and CreateDate<=dateadd(d,-1,@eRq)),0)--昨日银行合计
			
			Set @IBEA=ISNULL(( SELECT SUM(income) FROM [Finance_RunningAccount] WHERE  CompanyId=@CompanyId AND (
	 PaymentTypeId=3 OR 
(BankId IN (SELECT fb.id FROM Finance_Bank fb WHERE fb.CompanyId=@CompanyId AND fb.PaymentTypeId=2 ) )
)

  AND CreateDate>=@sRq and CreateDate<=@eRq ),0)--ISNULL((Select Sum(Income) From Finance_RunningAccount Where CompanyId=@companyId and Convert(nchar(10),CreateDate,120)=@CreateDate and PaymentTypeId<>1),0)--当日银行收入
			
			Set @IBEB=ISNULL((SELECT sum(outlayprice)
  FROM [Finance_Outlay]   
WHERE  companyidcur=@CompanyId AND [cb_flag]=0  and (
	 PaymentTypeId=3 OR 
(BankId IN (SELECT fb.id FROM Finance_Bank fb WHERE fb.PaymentTypeId=2 AND fb.CompanyId=@CompanyId) )
) and  CONVERT(VARCHAR(10), createdate,120)=@CreateDate  ),0)
--支出审核时生成的成本类支出不在现金银行中减少 2015-1-15 注释不用，支出记录中用[cb_flag]标识标记是否参与计算
---isnull((SELECT SUM(costprice) FROM Project p WHERE p.CompanyId=@CompanyId AND  p.FinanceAuditDate>=@sRq and p.FinanceAuditDate<=@eRq
--AND p.FinanceAudit=2 AND (
--	(Select PaymentTypeId From [Order] Where [Order].companyid=@CompanyId and OId=p.OrderId)=3 OR 
--(Select PaymentTypeId From [Order] Where [Order].companyid=@CompanyId AND OId=p.OrderId and [Order].BankId IN (SELECT fb.id FROM Finance_Bank fb WHERE fb.CompanyId=@CompanyId AND fb.PaymentTypeId=2 ) )=2
-- )),0)--ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=@companyId and Convert(nchar(10),CreateDate,120)=@CreateDate and PaymentTypeId<>1),0)--当日银行支出
			
			Set @IBE=@YIBE+(@IBEA-@IBEB)+@IBI+ --对冲
ISNULL((SELECT sum(dch_yy) FROM Order_Change_Hedge WHERE  CompanyId=@CompanyId AND UpTime>=@sRq and UpTime<=@eRq),0) 
+
--存现-提现
(isnull((SELECT sum(cx) FROM txcx t WHERE CompanyId=@CompanyId AND CreateDate>=@sRq and CreateDate<=@eRq AND  (
	t.cx_PaymentTypeId =3 OR 
t.cx_BankId IN (SELECT fb.id FROM Finance_Bank fb WHERE  fb.CompanyId=@CompanyId AND fb.PaymentTypeId=2 ) 
 )   ),0)
	-
isnull((SELECT sum(tx) FROM txcx t WHERE CompanyId=@CompanyId AND CreateDate>=@sRq and CreateDate<=@eRq AND   (
	t.tx_PaymentTypeId=3 OR 
t.tx_BankId IN (SELECT fb.id FROM Finance_Bank fb WHERE fb.CompanyId=@CompanyId AND fb.PaymentTypeId=2 )
 )   ),0)
 +
 ISNULL(( SELECT SUM([Income])  FROM Finance_RunningAccount_Non WHERE Companyid=@Companyid and createdate>=@sRq and createdate<=@eRq 
 AND (PaymentTypeId=3 or BankId IN (SELECT id FROM Finance_Bank fb WHERE Companyid=@Companyid and fb.PaymentTypeId=2 ))),0)
)--应有银行
			
			--(3)资金合计-------------------------
			Set @IBG=@IBD+@IBE--资金合计
			
			--(4)差额-----------------------------
			--Set @IBJ=ISNULL((Select IBA+IBB From Statistics_Financial Where CompanyId=@companyId and Convert(nchar(10),CreateDate,120)=@CreateDate),0)-@IBG--差额
			
			/*    原差额公式 2015-1-15起用新公式
			--        （今日资金-昨日资金）-  (新增毛利)+(续费毛利)+(尾款收入)-(新增应收)+(新增应付)+(现金平整)+(银行平整) - (当日账户余额) +   （昨日账户余额）    -   （支出）-补划 +（还款）+(公司往来收入)+(非营业性收入)
			Set @IBJ=(isnull((SELECT IBA+ibb FROM Statistics_Financial sf WHERE CompanyId=@companyId and  CreateDate>=@sRq and CreateDate<=@eRq ),0)
			-isnull((SELECT IBA+ibb FROM Statistics_Financial sf WHERE CompanyId=@companyId and CreateDate>=dateadd(d,-1,@sRq) and CreateDate<=dateadd(d,-1,@eRq)),0))-
			((@IAA+      @IAB+      @IAF-     @IAC+      @IAD+      @IBH+      @IBI
			-( ISNULL((SELECT IBC FROM Statistics_Financial WHERE CompanyId=@companyId and  CreateDate>=@sRq and CreateDate<=@eRq ),0))
			+( ISNULL((SELECT IBC FROM Statistics_Financial WHERE CompanyId=@companyId and CreateDate>=dateadd(d,-1,@sRq) and CreateDate<=dateadd(d,-1,@eRq)),0))
			- ISNULL((SELECT sum(fo.OutlayPrice) FROM Finance_Outlay fo WHERE CompanyIdcur=@companyId and fo.CreateDate>=@sRq and fo.CreateDate<=@eRq  AND CateOneId!=19),0)
			-ISNULL((Select Sum(CostPrice) From Project Where CompanyId =@CompanyId and FinanceAuditDate>=@sRq and FinanceAuditDate<=@eRq  AND CreateDate<@sRq and FinanceAudit=2  ),0) --补划 
			+ISNULL((SELECT sum(l.[Money]) FROM Loan l WHERE l.flag=1 and  l.HKDate>=@sRq and l.HKDate<=@eRq AND l.CompanyId=@CompanyId),0)
			
			+ISNULL((SELECT SUM(ld.je)  FROM L_Dealings ld WHERE ld.I_CompanyId=@CompanyId AND ld.CreateDate>=@sRq and ld.CreateDate<=@eRq),0)
			+@IAG
			))
			*/
			/******************差额公式2015-1-15*************************/
declare @zrzj DECIMAL(18,2)	,@fy decimal(18,2),@cbzc decimal(18,2),@jkzc decimal(18,2),@jksr decimal(18,2),@tzzc decimal(18,2),@tzsr decimal(18,2),@fh decimal(18,2),@dk decimal(18,2),@zrdk decimal(18,2),@dkzc decimal(18,2),@dksr decimal(18,2),@drzj decimal(18,2),@bhcb decimal(18,2),@drzhye decimal(18,2),@zrzhye decimal(18,2),@fyyxsr_rb decimal(18,2),@ssclwsr decimal(18,2)
	
set @zrzj=	isnull((SELECT IBA+ibb FROM Statistics_Financial sf WHERE CompanyId=@companyId and 
CreateDate between dateadd(d,-1,@sRq) and dateadd(d,-1,@eRq)),0)
set @fy=isnull((select sum(OutlayPrice) from Finance_Outlay where companyidcur=@companyId and createdate between  @sRq and @eRq 
AND CateOneId!=19 and Subjectid not in (196,201,203,206,243)),0)
--+
--ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1  
--and  CreateDate>=@sRq and CreateDate<=@eRq  and Subjectid not in (196,203,206,243,166,171,173,244) ),0)

set @cbzc=isnull((select sum(CostPrice) from Project where CompanyId=@companyId and FinanceAuditDate between @sRq and @eRq),0)
set @jkzc=isnull((select sum(OutlayPrice) from Finance_Outlay where companyidcur=@companyId and createdate between  @sRq and @eRq
AND Subjectid=203),0)
--+
--ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1  
--and  CreateDate>=@sRq and CreateDate<=@eRq  and Subjectid=203  ),0)

set @jksr=isnull((select sum(income) from Finance_RunningAccount where companyid=@companyId and 
createdate between  @sRq and @eRq  and charindex('[借款收入]',Summary,0)>0),0)
--+
--ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=2  
--and  CreateDate>=@sRq and CreateDate<=@eRq  and AccountType=1  ),0)

set @tzzc=isnull((select sum(OutlayPrice) from Finance_Outlay where companyidcur=@companyId and createdate between  @sRq and @eRq
AND Subjectid=206),0)
--+
--ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1  
--and  CreateDate>=@sRq and CreateDate<=@eRq  and Subjectid=206  ),0)

set @tzsr=isnull((select sum(income) from Finance_RunningAccount_Non where companyid=@companyId and accountstypeid=3 
and createdate between  @sRq and @eRq),0)
--+
--ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=2  
--and  CreateDate>=@sRq and CreateDate<=@eRq  and AccountType=3  ),0)

set @fh=isnull((select sum(OutlayPrice) from Finance_Outlay where companyidcur=@companyId and createdate between  @sRq and @eRq
AND Subjectid=196),0)
--+
--ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1  
--and  CreateDate>=@sRq and CreateDate<=@eRq  and Subjectid=196  ),0)

--2016-03-29 增加QQ渠道非企支出
Set @qqqdfqzcml=ISNULL((Select Sum(SalePrice-CostPrice) From Project Where CompanyId=@companyId and  CreateDate>=@sRq and CreateDate<=@eRq  and TypeId=1 AND ProductId not IN (22,29,33) and SalePrice<>CostPrice  AND Project.NOE_Flag=1),0)+ 
ISNULL((SELECT sum(dch_xzml) FROM Order_Change_Hedge inner JOIN [Order] o on Order_Change_Hedge.oid = o.oid and Order_Change_Hedge.CompanyId=@companyId and UpTime>=@sRq and UpTime<=@eRq  and o.companyId=@companyId AND o.NOE_Flag=1),0)+
ISNULL((Select Sum(SalePrice-CostPrice) From Project Where CompanyId=@companyId and  CreateDate>=@sRq and CreateDate<=@eRq  and TypeId=2 AND ProductId not IN (22,29,33) and SalePrice<>CostPrice  AND Project.NOE_Flag=1),0)+
ISNULL((SELECT sum(dch_xfml) FROM Order_Change_Hedge inner join [Order] o on Order_Change_Hedge.oid =o.oid and Order_Change_Hedge.CompanyId=@companyId and UpTime>=@sRq and UpTime<=@eRq  and o.companyId=@companyId AND o.NOE_Flag=1),0)

Set @qqqdfqzcxse=ISNULL((Select Sum(SalePrice) From Project Where CompanyId=@companyId and  CreateDate>=@sRq and CreateDate<=@eRq  and TypeId=1 AND ProductId not IN (22,29,33) and SalePrice<>CostPrice  AND Project.NOE_Flag=1),0)+ 
ISNULL((SELECT sum(dch_chd) FROM Order_Change_Hedge inner JOIN [Order] o on Order_Change_Hedge.oid = o.oid and Order_Change_Hedge.CompanyId=@companyId and UpTime>=@sRq and UpTime<=@eRq  and o.companyId=@companyId AND o.NOE_Flag=1),0)+
ISNULL((Select Sum(SalePrice) From Project Where CompanyId=@companyId and  CreateDate>=@sRq and CreateDate<=@eRq  and TypeId=2 AND ProductId not IN (22,29,33) and SalePrice<>CostPrice  AND Project.NOE_Flag=1),0)+
ISNULL((SELECT sum(dch_chd) FROM Order_Change_Hedge inner join [Order] o on Order_Change_Hedge.oid =o.oid and Order_Change_Hedge.CompanyId=@companyId and UpTime>=@sRq and UpTime<=@eRq  and o.companyId=@companyId AND o.NOE_Flag=1),0)


--2016-02-29 改多款为所有多款，含未来时间被认领的
DECLARE @dkqt decimal(18,2)
set @dk=isnull((select sum(income) from Finance_RunningAccount where companyid=@companyId and renling=1 
and createdate <= @eRq 
),0) 

set @dkqt=isnull((select sum(income) from Finance_RunningAccount where companyid=@companyId and renling=2 
and createdate <= @eRq  and renlingTime>@eRq
),0) 

set @dk= @dk + @dkqt
--2016-03-01 增加昨日多款，差额计算用
set @zrdk = isnull((Select c14 from Statistics_Financial_CE where companyid=@companyId and convert(varchar(10),createdate,120)=convert(varchar(10),dateadd(day,-1,@eRq),120) ),0)


set @dkzc=isnull((select sum(OutlayPrice) from Finance_Outlay where companyidcur=@companyId and createdate between  @sRq and @eRq
AND Subjectid=243),0)
--+
--ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1  
--and  CreateDate>=@sRq and CreateDate<=@eRq  and Subjectid=243  ),0)

set @dksr=isnull((select sum(income) from Finance_RunningAccount_Non where companyid=@companyId  and accountstypeid=2 
and createdate between  @sRq and @eRq),0)
--+
--ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=2  
--and  CreateDate>=@sRq and CreateDate<=@eRq  and AccountType=2  ),0)

set @drzj=isnull((SELECT IBA+ibb FROM Statistics_Financial sf WHERE CompanyId=@companyId and  CreateDate between  @sRq and @eRq ),0)
set @bhcb= ISNULL((Select Sum(CostPrice) From Project Where CompanyId =@CompanyId and FinanceAudit=2 and FinanceAuditDate>=@sRq and FinanceAuditDate<=@eRq  AND CreateDate<@sRq  ),0)
set @fyyxsr_rb=isnull((select sum(income) from Finance_RunningAccount_Non where companyid=@companyId  and accountstypeid=4 
and createdate between  @sRq and @eRq),0)+
					ISNULL((SELECT SUM(p.SalePrice-p.CostPrice) FROM Project p WHERE p.ProductId=33 AND p.CompanyId=@CompanyId AND CreateDate>=@sRq and CreateDate<=@eRq),0)
--+
--ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=2  
--and  CreateDate>=@sRq and CreateDate<=@eRq  and AccountType=4  ),0)

set @ssclwsr=isnull((select sum(income) from Finance_RunningAccount_Non where companyid=@companyId  and accountstypeid=5 
and createdate between  @sRq and @eRq),0)
--+
--ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=2  
--and  CreateDate>=@sRq and CreateDate<=@eRq  and AccountType=5  ),0)

set @drzhye=ISNULL((SELECT IBC FROM Statistics_Financial WHERE CompanyId=@companyId and  CreateDate>=@sRq and CreateDate<=@eRq ),0)
set @zrzhye=ISNULL((SELECT IBC FROM Statistics_Financial WHERE CompanyId=@companyId and CreateDate>=dateadd(d,-1,@sRq) and CreateDate<=dateadd(d,-1,@eRq)),0)

--奖金、补偿返款 2016-03-28将原jjbcfk拆分为jj和bcfk
set @jj=isnull((select sum(income) from Finance_RunningAccount_Non where companyid=@companyId and AccountsTypeId=6 and createdate between  @sRq and @eRq),0)+
		ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=2  
			and  CreateDate>=@sRq and CreateDate<=@eRq  and AccountType=6  ),0)

set @bcfk=isnull((select sum(income) from Finance_RunningAccount_Non where companyid=@companyId and AccountsTypeId=7 and createdate between  @sRq and @eRq),0)+
			ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=2  
			and  CreateDate>=@sRq and CreateDate<=@eRq  and AccountType=7  ),0)
--+
--ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=2  
--and  CreateDate>=@sRq and CreateDate<=@eRq  and AccountType=6  ),0)


/*2015-1-27 修改差额公式：(当日资金-昨日资金)-（新增毛利+续费毛利-费用+尾款-新增应收+新增应付-补划成本-借款支出+借款收入
    -调款支出+调款收入-投资支出+投资收入-分红+未认多款+非营业性收入+实收纯利外收入
    -当日账户余额+昨日账户余额）*/
Set @IBJ=
--当日资金
(@drzj
-
--昨日资金
@zrzj)
-
--新增毛利
(@IAA
--ISNULL((Select Sum(SalePrice-CostPrice) From Project Where CompanyId=@companyId and  CreateDate>=@sRq and CreateDate<=@eRq  and TypeId=1 AND ProductId not IN (22,29,33) and SalePrice<>CostPrice  AND Project.NOE_Flag<>2 ),0)+
--ISNULL((SELECT sum(dch_xzml) FROM Order_Change_Hedge inner JOIN [Order] o on Order_Change_Hedge.oid = o.oid and Order_Change_Hedge.CompanyId=@companyId and UpTime>=@sRq and UpTime<=@eRq  and o.companyId=@companyId AND o.NOE_Flag<>2 ),0)--新增毛利
+		
--续费毛利	
@IAB
--ISNULL((Select Sum(SalePrice-CostPrice) From Project Where CompanyId=@companyId and  CreateDate>=@sRq and CreateDate<=@eRq  and TypeId=2 AND ProductId not IN (22,29,33) and SalePrice<>CostPrice  AND Project.NOE_Flag<>2 ),0)+
--ISNULL((SELECT sum(dch_xfml) FROM Order_Change_Hedge inner join [Order] o on Order_Change_Hedge.oid =o.oid and Order_Change_Hedge.CompanyId=@companyId and UpTime>=@sRq and UpTime<=@eRq  and o.companyId=@companyId AND o.NOE_Flag<>2),0)--续费毛利
---
----成本支出
--@cbzc
-- 非企支出
+

@qqqdfqzcml
 
-
--费用 (成本类,分红,借款支出,投资支出,调款支出 不算费用)
@fy
+
--尾款收入
@IAF
--ISNULL((Select Sum(Prices) From Order_PaymentRecord inner JOIN [Order] o on  Order_PaymentRecord.oid = o.oid and Order_PaymentRecord.CompanyId=@companyId and FinanceDate>=@sRq and FinanceDate<=@eRq and SWId=2 and  o.companyId=@companyId AND o.NOE_Flag<>2),0)+
--ISNULL((SELECT sum(dch_wk) FROM Order_Change_Hedge inner JOIN [Order] o on Order_Change_Hedge.oid = o.oid and Order_Change_Hedge.CompanyId=@companyId and UpTime>=@sRq and UpTime<=@eRq and o.companyId=@companyId AND o.NOE_Flag<>2 ),0)--尾款收入
-			 
--新增应收
@IAC
--ISNULL((Select Sum(h_yingshou) From [Order] WHERE CompanyId=@CompanyId and AuditDate>=@sRq and AuditDate<=@eRq AND StateId>=1  AND NOE_Flag<>2 ),0)+
--ISNULL((SELECT sum(dch_ysh) FROM Order_Change_Hedge inner join [Order] o on Order_Change_Hedge.oid =o.oid  and Order_Change_Hedge.CompanyId=@companyId and UpTime>=@sRq and UpTime<=@eRq  and o.companyId=@companyId AND o.NOE_Flag<>2 ),0)--新增应收
+
--新增应付
@IAD
--ISNULL((Select Sum(CostPrice) From Project Where CompanyId =@CompanyId and FinanceAudit=1 and  CreateDate>=@sRq and CreateDate<=@eRq ),0)+
--ISNULL((SELECT sum(dch_yf) FROM Order_Change_Hedge WHERE CompanyId=@companyId and UpTime>=@sRq and UpTime<=@eRq  ),0)   --新增应付
-
--补划成本
@bhcb
-
--借款支出
@jkzc

+
--借款收入
@jksr

-
--投资支出
@tzzc

+
--投资收入
@tzsr

-
--分红
@fh

+

--未认多款,2016-03-01 由+@dk 改为 @dk-@zrdk

@dk
-
@zrdk

-
--调款支出
@dkzc

+
--调款收入
@dksr
+
--非营业性收入
@fyyxsr_rb
+
--实收纯利外收入
@ssclwsr
-
--当日账户余额
@drzhye
+
--昨日账户余额
@zrzhye
+
--奖金补偿返款
@jj
+
@bcfk
)

--删除资金差额表中的当日记录，然后重新插入一条新记录
delete [Statistics_Financial_CE] where companyid=@CompanyId and createdate between @sRq and @eRq

INSERT INTO [Statistics_Financial_CE]
           ([companyid]
           ,[createdate]
           ,[c1]           ,[c2]           ,[c3]
           ,[c4]           ,[c5]           ,[c6]
           ,[c7]           ,[c8]           ,[c9]
           ,[c10]           ,[c11]           ,[c12]
           ,[c13]           ,[c14]           ,[c15]
           ,[c16]           ,[zje],c17,companyname
           ,[c18]           ,[c19]           ,[c20]
            ,[c21] ,[c22],[zrdk],[fqzc])
 values(@CompanyId,@CreateDate,@zrzj,@IAA,@IAB,@cbzc,@fy,@IAF,@IAC,@IAD,@jkzc,@jksr,@tzzc,@tzsr,@fh,
 @dk,@dkzc,@dksr,@IBJ,@drzj,@CompanyName
 ,@drzhye,@zrzhye,@fyyxsr_rb+@jj+@bcfk,@bhcb,@ssclwsr,@zrdk,@qqqdfqzcml
 )          
           
           			
	/******************差额公式2015-1-15*************************/		
			
--(5)其他-----------------------------
			
--Set @IBF=ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=@companyId and SubjectId=203),0)--借款合计
Set @IBF=ISNULL((Select Sum(l.[Money]) FROM Loan l Where l.CompanyId=@companyId ),0)
+ISNULL((Select Sum(ld.je) FROM L_Dealings ld Where ld.O_CompanyId=@companyId and  CreateDate>=@sRq and CreateDate<=@eRq  and SubjectId=203),0)
--借款合计
			
Set @IBK=ISNULL((Select Sum(IAC-IAF) From Statistics_Financial Where CompanyId=@companyId),0)--应收合计
			
Set @IBL=ISNULL((Select Sum(IAD) From Statistics_Financial Where CompanyId=@companyId),0)--应付合计
			
/************************↓成单数统计↓********************************
2015-01-22 统计单数，用于月报统计
*/
DECLARE @bd_je DECIMAL(18,2),@wz_je DECIMAL(18,2),@qyQQ_je  DECIMAL(18,2),@qt_je  DECIMAL(18,2),@idc_je DECIMAL(18,2)
	DECLARE @bd int,@wz int,@qt INT,@qyQQ INT,@QYQQ_CustomerCount INT,@idc INT	
	declare @cl DECIMAL(18,2),@sscl DECIMAL(18,2)
	declare @yggz DECIMAL(18,2)	,@sf decimal(18,2),@ff decimal(18,2),@zbfy decimal(18,2),@ygfl decimal(18,2),@sjglf decimal(18,2),@qtzc  decimal(18,2),@fyze   decimal(18,2)
	declare @fyyxsr decimal(18,2),@ssclwzc decimal(18,2),@gdzc int,@rcfy DECIMAL(18,2),@zxf DECIMAL(18,2)

declare @bd_je1 DECIMAL(18,2),@qyQQ_je1 DECIMAL(18,2)
set @bd_je1=0
set @qyQQ_je1=0
--百度单数
set @bd=(isnull((Select Count(Distinct convert(varchar(50),OrderId)+convert(varchar(11),ProductId)) From Project Where CreateDate>=@sRq And CreateDate<=@eRq And CompanyId =@companyId 
And NOE_Flag=0 And TypeId=1 And SalePrice!=0 
	--And ProductId in (select id from product where f_flag='bd') 
	And ProductId IN (SELECT ID FROM Product WHERE CateOneId = 1 AND CateTwoId IN (10) AND ID NOT IN (37,171))	--与产品销售统计类型上一致
	And (IsHedge=0 Or IsHedge=-2)),0) 
- (isnull((Select Count(Distinct convert(varchar(50),OrderId)+convert(varchar(11),ProductId)) From Project Where CreateDate>=@sRq And CreateDate<=@eRq 
And CompanyId =@companyId And NOE_Flag=0 And TypeId=1 And SalePrice!=0 
	--And ProductId in (select id from product where f_flag='bd') 
	And ProductId IN (SELECT ID FROM Product WHERE CateOneId = 1 AND CateTwoId IN (10) AND ID NOT IN (37,171))	--与产品销售统计类型上一致
	And IsHedge=-1),0))) 
--百度金额
set @bd_je=(isnull((Select sum( saleprice) From Project Where CreateDate>=@sRq And CreateDate<=@eRq And CompanyId =@companyId 
And NOE_Flag=0 And TypeId=1  
	--And ProductId in (select id from product where f_flag='bd') 
	And ProductId IN (SELECT ID FROM Product WHERE CateOneId = 1 AND CateTwoId IN (10) AND ID NOT IN (37,171))	--与产品销售统计类型上一致
	And (IsHedge=0 Or IsHedge=-2)),0) 
+ (isnull((Select sum(saleprice) From Project Where CreateDate>=@sRq And CreateDate<=@eRq 
And CompanyId =@companyId And NOE_Flag=0 And TypeId=1  
	--And ProductId in (select id from product where f_flag='bd') 
	And ProductId IN (SELECT ID FROM Product WHERE CateOneId = 1 AND CateTwoId IN (10) AND ID NOT IN (37,171))	--与产品销售统计类型上一致
	And IsHedge=-1),0))) 
--百度服务费
set @bd_je1=(isnull((Select sum( saleprice) From Project Where CreateDate>=@sRq And CreateDate<=@eRq And CompanyId =@companyId 
And NOE_Flag=0 And TypeId=1  
	--And ProductId in (select id from product where f_flag='bdxse') 
	AND ProductId IN (SELECT ID FROM Product WHERE CateOneId = 1 AND CateTwoId IN (10) AND ID IN (37,171))		--与产品销售统计类型上一致
	And (IsHedge=0 Or IsHedge=-2)),0) 
+ (isnull((Select sum(saleprice) From Project Where CreateDate>=@sRq And CreateDate<=@eRq 
And CompanyId =@companyId And NOE_Flag=0 And TypeId=1  
	--And ProductId in (select id from product where f_flag='bdxse') 
	AND ProductId IN (SELECT ID FROM Product WHERE CateOneId = 1 AND CateTwoId IN (10) AND ID IN (37,171))		--与产品销售统计类型上一致
	And IsHedge=-1),0))) 
--百度服务费不算成单数，只在统计销售额时参与计算
set @bd_je=@bd_je+@bd_je1

--网站单数  in (select id from Product where flag=1 ) 
set @wz=(isnull((Select Count(Distinct convert(varchar(50),OrderId)+convert(varchar(11),ProductId)) From Project Where CreateDate>=@sRq And CreateDate<=@eRq And CompanyId =@companyId 
And NOE_Flag=0 And TypeId=1 And SalePrice!=0 
	--And ProductId in (select id from product where f_flag='wz') 
	And ProductId IN (SELECT ID FROM Product WHERE CateOneId = 1 AND CateTwoId IN (7))		--与产品销售统计类型上一致
	And (IsHedge=0 Or IsHedge=-2)),0) 
- (isnull((Select Count(Distinct convert(varchar(50),OrderId)+convert(varchar(11),ProductId)) From Project Where CreateDate>=@sRq And CreateDate<=@eRq 
And CompanyId =@companyId And NOE_Flag=0 And TypeId=1 And SalePrice!=0 
	--And ProductId in (select id from product where f_flag='wz' ) 
	And ProductId IN (SELECT ID FROM Product WHERE CateOneId = 1 AND CateTwoId IN (7))		--与产品销售统计类型上一致
	And IsHedge=-1),0))) 
--网站金额
set @wz_je=(isnull((Select sum( saleprice) From Project Where CreateDate>=@sRq And CreateDate<=@eRq And CompanyId =@companyId 
And NOE_Flag=0 And TypeId=1  
	--And ProductId in (select id from product where f_flag='wz') 
	And ProductId IN (SELECT ID FROM Product WHERE CateOneId = 1 AND CateTwoId IN (7))		--与产品销售统计类型上一致
	And (IsHedge=0 Or IsHedge=-2)),0) 
+ (isnull((Select sum(saleprice) From Project Where CreateDate>=@sRq And CreateDate<=@eRq 
And CompanyId =@companyId And NOE_Flag=0 And TypeId=1  
	--And ProductId in (select id from product where f_flag='wz') 
	And ProductId IN (SELECT ID FROM Product WHERE CateOneId = 1 AND CateTwoId IN (7))		--与产品销售统计类型上一致
	And IsHedge=-1),0)))
----企业QQ单数68,71,112,116
set @qyQQ=(isnull((Select Count(Distinct convert(varchar(50),OrderId)+convert(varchar(11),ProductId)) From Project Where CreateDate>=@sRq And CreateDate<=@eRq And CompanyId =@companyId 
And NOE_Flag=0 And TypeId=1 And SalePrice!=0 And ProductId in (71,112,281,200,201) And (IsHedge=0 Or IsHedge=-2)),0) 
- (isnull((Select Count(Distinct convert(varchar(50),OrderId)+convert(varchar(11),ProductId)) From Project Where CreateDate>=@sRq And CreateDate<=@eRq 
And CompanyId =@companyId And NOE_Flag=0 And TypeId=1 And SalePrice!=0 And ProductId in (71,112,281,200,201) And IsHedge=-1),0)))
--企业QQ客户数
SELECT @QYQQ_CustomerCount = COUNT(1)
FROM (
	SELECT CusId,SUM(OrderCount) OrderSum
	FROM (
		SELECT CusId,COUNT(DISTINCT CONVERT(VARCHAR(50),OrderID) + CONVERT(VARCHAR(20),ProductID)) AS OrderCount
		FROM Project
		WHERE CreateDate>=@sRq And CreateDate<=@eRq And CompanyId =@companyId And NOE_Flag=0 And TypeId=1 And SalePrice!=0 
			And ProductId in (71,112,281,200,201) And (IsHedge=0 Or IsHedge=-2)
		GROUP BY CusId

		UNION ALL

		SELECT CusId,-COUNT(DISTINCT CONVERT(VARCHAR(50),OrderID) + CONVERT(VARCHAR(20),ProductID)) AS OrderCount
		FROM Project
		WHERE CreateDate>=@sRq And CreateDate<=@eRq And CompanyId =@companyId And NOE_Flag=0 And TypeId=1 And SalePrice!=0 
			And ProductId in (71,112,281,200,201) And IsHedge=-1
		GROUP BY CusId
		) AS T1
	GROUP BY CusId
	HAVING SUM(OrderCount) > 0
	) AS T2	  
--企业QQ金额
set @qyQQ_je=(isnull((Select sum( saleprice) From Project Where CreateDate>=@sRq And CreateDate<=@eRq And CompanyId =@companyId 
And NOE_Flag=0 And TypeId=1  And ProductId in (71,112,281,200,201) And (IsHedge=0 Or IsHedge=-2)),0) 
+ (isnull((Select sum(saleprice) From Project Where CreateDate>=@sRq And CreateDate<=@eRq 
And CompanyId =@companyId And NOE_Flag=0 And TypeId=1  And ProductId in (71,112,281,200,201) And IsHedge=-1),0))) 

set @qyQQ_je1=(isnull((Select sum( saleprice) From Project Where CreateDate>=@sRq And CreateDate<=@eRq And CompanyId =@companyId 
And NOE_Flag=0 And TypeId=1  And ProductId in (select id from product where f_flag='qyqqxse') And (IsHedge=0 Or IsHedge=-2)),0) 
+ (isnull((Select sum(saleprice) From Project Where CreateDate>=@sRq And CreateDate<=@eRq 
And CompanyId =@companyId And NOE_Flag=0 And TypeId=1  And ProductId in (select id from product where f_flag='qyqqxse') And IsHedge=-1),0))) 
--企业QQ服务费不算成单数，只在统计销售额时参与计算
set @qyQQ_je=@qyQQ_je+@qyQQ_je1

----其他产品单数
set @qt=(isnull((Select Count(Distinct convert(varchar(50),OrderId)+convert(varchar(11),ProductId)) From Project Where CreateDate>=@sRq And CreateDate<=@eRq And CompanyId =@companyId 
And NOE_Flag=0 And TypeId=1 And SalePrice!=0 
	--And ProductId in (select id from product where f_flag='qt') 
	AND ProductId IN (SELECT ID FROM Product WHERE CateOneId = 1 AND CateTwoId IN (14,40,51))		--与产品销售统计类型上一致
	And (IsHedge=0 Or IsHedge=-2)),0) 
- (isnull((Select Count(Distinct convert(varchar(50),OrderId)+convert(varchar(11),ProductId)) From Project Where CreateDate>=@sRq And CreateDate<=@eRq 
And CompanyId =@companyId And NOE_Flag=0 And TypeId=1 And SalePrice!=0 
	--And ProductId in (select id from product where f_flag='qt') 
	AND ProductId IN (SELECT ID FROM Product WHERE CateOneId = 1 AND CateTwoId IN (14,40,51))		--与产品销售统计类型上一致
	And IsHedge=-1),0))) 
--其他产品金额
set @qt_je=(isnull((Select sum( saleprice) From Project Where CreateDate>=@sRq And CreateDate<=@eRq And CompanyId =@companyId 
And NOE_Flag=0 And TypeId=1  
	--And ProductId in (select id from product where f_flag='qt') 
	AND ProductId IN (SELECT ID FROM Product WHERE CateOneId = 1 AND CateTwoId IN (14,40,51))		--与产品销售统计类型上一致
	And (IsHedge=0 Or IsHedge=-2)),0) 
+ (isnull((Select sum(saleprice) From Project Where CreateDate>=@sRq And CreateDate<=@eRq 
And CompanyId =@companyId And NOE_Flag=0 And TypeId=1  
	--And ProductId in (select id from product where f_flag='qt') 
	AND ProductId IN (SELECT ID FROM Product WHERE CateOneId = 1 AND CateTwoId IN (14,40,51))		--与产品销售统计类型上一致
	And IsHedge=-1),0))) 
--IDC产品单数
set @idc=(isnull((Select Count(Distinct convert(varchar(50),OrderId)+convert(varchar(11),ProductId)) From Project Where CreateDate>=@sRq And CreateDate<=@eRq And CompanyId =@companyId 
And NOE_Flag=0 And TypeId=1 And SalePrice!=0 
	--And ProductId in (select id from product where f_flag='idc') 
	AND ProductId IN (SELECT ID FROM Product WHERE CateOneId = 1 AND CateTwoId IN (8,9,11,32,12))		--与产品销售统计类型上一致
	And (IsHedge=0 Or IsHedge=-2)),0) 
- (isnull((Select Count(Distinct convert(varchar(50),OrderId)+convert(varchar(11),ProductId)) From Project Where CreateDate>=@sRq And CreateDate<=@eRq 
And CompanyId =@companyId And NOE_Flag=0 And TypeId=1 And SalePrice!=0 
	--And ProductId in (select id from product where f_flag='idc') 
	AND ProductId IN (SELECT ID FROM Product WHERE CateOneId = 1 AND CateTwoId IN (8,9,11,32,12))		--与产品销售统计类型上一致
	And IsHedge=-1),0))) 
--IDC产品金额
set @idc_je=(isnull((Select sum( saleprice) From Project Where CreateDate>=@sRq And CreateDate<=@eRq And CompanyId =@companyId 
And NOE_Flag=0 And TypeId=1  
	--And ProductId in (select id from product where f_flag='idc') 
	AND ProductId IN (SELECT ID FROM Product WHERE CateOneId = 1 AND CateTwoId IN (8,9,11,32,12))		--与产品销售统计类型上一致
	And (IsHedge=0 Or IsHedge=-2)),0) 
+ (isnull((Select sum(saleprice) From Project Where CreateDate>=@sRq And CreateDate<=@eRq 
And CompanyId =@companyId And NOE_Flag=0 And TypeId=1  
	--And ProductId in (select id from product where f_flag='idc') 
	AND ProductId IN (SELECT ID FROM Product WHERE CateOneId = 1 AND CateTwoId IN (8,9,11,32,12))		--与产品销售统计类型上一致
	And IsHedge=-1),0))) 


--百度直通车单数
Declare @bdztc int,@bdztc_je decimal(18,2)
set @bdztc=(isnull((Select Count(Distinct convert(varchar(50),OrderId)+convert(varchar(11),ProductId)) From Project Where CreateDate>=@sRq And CreateDate<=@eRq And CompanyId =@companyId 
And NOE_Flag=0 And TypeId=1 And SalePrice!=0 
	--And ProductId =257 
	AND ProductId IN (SELECT ID FROM Product WHERE CateOneId = 1 AND CateTwoId IN (60))		--与产品销售统计类型上一致
	And (IsHedge=0 Or IsHedge=-2)),0) 
- (isnull((Select Count(Distinct convert(varchar(50),OrderId)+convert(varchar(11),ProductId)) From Project Where CreateDate>=@sRq And CreateDate<=@eRq 
And CompanyId =@companyId And NOE_Flag=0 And TypeId=1 And SalePrice!=0 
	--And ProductId =257 
	AND ProductId IN (SELECT ID FROM Product WHERE CateOneId = 1 AND CateTwoId IN (60))		--与产品销售统计类型上一致
	And IsHedge=-1),0))) 
--百度直通车金额
set @bdztc_je=(isnull((Select sum( saleprice) From Project Where CreateDate>=@sRq And CreateDate<=@eRq And CompanyId =@companyId 
And NOE_Flag=0 And TypeId=1  
	--And ProductId in (257,258) 
	AND ProductId IN (SELECT ID FROM Product WHERE CateOneId = 1 AND CateTwoId IN (60))		--与产品销售统计类型上一致
	And (IsHedge=0 Or IsHedge=-2)),0) 
+ (isnull((Select sum(saleprice) From Project Where CreateDate>=@sRq And CreateDate<=@eRq 
And CompanyId =@companyId And NOE_Flag=0 And TypeId=1  
	--And ProductId in (257,258) 
	AND ProductId IN (SELECT ID FROM Product WHERE CateOneId = 1 AND CateTwoId IN (60))		--与产品销售统计类型上一致
	And IsHedge=-1),0))) 


/*************************统计支出费用********************************/	
  
 --员工工资sum([OutlayPrice])
set @yggz=isnull((select sum([OutlayPrice]) from Finance_Outlay where companyidcur=@companyId and [SubjectId]=211 and createdate between @sRq and @eRq),0)
+ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1  
and  CreateDate>=@sRq and CreateDate<=@eRq  and Subjectid=211  ),0)
--税费
set @sf=isnull((select sum([OutlayPrice]) from Finance_Outlay where companyidcur=@companyId and [SubjectId]=213 and createdate between @sRq and @eRq),0)
+ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1  
and  CreateDate>=@sRq and CreateDate<=@eRq  and Subjectid=213  ),0)
 --日常费用
set @rcfy= isnull((select sum([OutlayPrice]) from Finance_Outlay where companyidcur=@companyId and [SubjectId] in (  217,218,215,219,220,221,222,223,224,225,226,228,240,241,230,242,232,233,175) and createdate between @sRq and @eRq),0)
+ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1  
and  CreateDate>=@sRq and CreateDate<=@eRq  and Subjectid in (  217,218,215,219,220,221,222,223,224,225,226,228,240,241,230,242,232,233,175)  ),0)
--房费
set @ff=isnull((select sum([OutlayPrice]) from Finance_Outlay where companyidcur=@companyId and [SubjectId]=214 and createdate between @sRq and @eRq),0)
+ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1  
and  CreateDate>=@sRq and CreateDate<=@eRq  and Subjectid=214  ),0)
--总办费用
set @zbfy=isnull((select sum([OutlayPrice]) from Finance_Outlay where companyidcur=@companyId and [SubjectId]=239 and createdate between @sRq and @eRq),0)
+ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1  
and  CreateDate>=@sRq and CreateDate<=@eRq  and Subjectid=239  ),0)
--员工福利
set @ygfl=isnull((select sum([OutlayPrice]) from Finance_Outlay where companyidcur=@companyId and [SubjectId]=235 and createdate between @sRq and @eRq),0)
+ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1  
and  CreateDate>=@sRq and CreateDate<=@eRq  and Subjectid=235  ),0)
--上缴管理费
set @sjglf=isnull((select sum([OutlayPrice]) from Finance_Outlay where companyidcur=@companyId and [SubjectId]=207 and createdate between @sRq and @eRq),0)
+ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1  
and  CreateDate>=@sRq and CreateDate<=@eRq  and Subjectid=207  ),0)
--固定资产及无形资产
set @gdzc=isnull((select sum([OutlayPrice]) from Finance_Outlay where companyidcur=@companyId and [SubjectId] in (176,174) and createdate between @sRq and @eRq),0)
+ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1  
and  CreateDate>=@sRq and CreateDate<=@eRq  and Subjectid in (176,174)  ),0)
--装修费
 set @zxf=isnull((select sum([OutlayPrice]) from Finance_Outlay where companyidcur=@companyId and [SubjectId]=231 and createdate between @sRq and @eRq),0)
 +ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1  
and  CreateDate>=@sRq and CreateDate<=@eRq  and Subjectid=231  ),0)
--其他支出
 set @qtzc=isnull((select sum([OutlayPrice]) from Finance_Outlay where companyidcur=@companyId and [SubjectId] =197 and createdate between @sRq and @eRq),0)
  +ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1  
and  CreateDate>=@sRq and CreateDate<=@eRq  and Subjectid=197  ),0)
--QQ渠道
--set @qqqd=isnull((select sum([OutlayPrice]) from Finance_Outlay where companyidcur=@companyId and [SubjectId] =246 and createdate between @sRq and @eRq),0)
--  +ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1  
--and  CreateDate>=@sRq and CreateDate<=@eRq  and Subjectid=197  ),0)

--跨区罚款
set @kqfk=isnull((select sum([OutlayPrice]) from Finance_Outlay where companyidcur=@companyId and [SubjectId] =247 and createdate between @sRq and @eRq),0)
  +ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1  
and  CreateDate>=@sRq and CreateDate<=@eRq  and Subjectid=247  ),0)
--费用总额
set @fyze=@yggz+@sf+@ff+@zbfy+@ygfl+@sjglf+@gdzc+@zxf+@qtzc+@rcfy+@kqfk+@OEB

/**************************支出费用********************************/

/*********************************************************/
--调款支出
set @dkzc=isnull((select sum(OutlayPrice) from Finance_Outlay where companyidcur=@companyId and createdate between  @sRq and @eRq
AND Subjectid=243),0)+
		ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1  
and  CreateDate>=@sRq and CreateDate<=@eRq  and Subjectid=243  ),0)
--调款收入
set @dksr=isnull((select sum(income) from Finance_RunningAccount_Non where companyid=@companyId  and accountstypeid=2 
and createdate between  @sRq and @eRq),0)+
		ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=2  
and  CreateDate>=@sRq and CreateDate<=@eRq  and AccountType=2  ),0)
--借款支出
set @jkzc=isnull((select sum(OutlayPrice) from Finance_Outlay where companyidcur=@companyId and createdate between  @sRq and @eRq
AND Subjectid=203),0)+
			ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1  
and  CreateDate>=@sRq and CreateDate<=@eRq  and Subjectid=203  ),0)
--借款收入
set @jksr=isnull((select sum(income) from Finance_RunningAccount where companyid=@companyId and 
createdate between  @sRq and @eRq  and charindex('[借款收入]',Summary,0)>0),0)+
		ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=2  
and  CreateDate>=@sRq and CreateDate<=@eRq  and AccountType=1  ),0)
--投资支出
set @tzzc=isnull((select sum(OutlayPrice) from Finance_Outlay where companyidcur=@companyId and createdate between  @sRq and @eRq
AND Subjectid=206),0)+
		ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1  
and  CreateDate>=@sRq and CreateDate<=@eRq  and Subjectid=206  ),0)
--投资收入
set @tzsr=isnull((select sum(income) from Finance_RunningAccount_Non where companyid=@companyId and accountstypeid=3 
and createdate between  @sRq and @eRq),0)+
          ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=2  
and  CreateDate>=@sRq and CreateDate<=@eRq  and AccountType=3  ),0)
--实收纯利外收入
set @ssclwsr=isnull((select sum(income) from Finance_RunningAccount_Non where companyid=@companyId and AccountsTypeId=5 and createdate between  @sRq and @eRq),0)+
			ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=2  
and  CreateDate>=@sRq and CreateDate<=@eRq  and AccountType=5  ),0)
--实收纯利外支出
set @ssclwzc=isnull((select sum(OutlayPrice) from Finance_Outlay where companyidcur=@companyId and createdate between  @sRq and @eRq
AND Subjectid=245),0)+
			ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1  
and  CreateDate>=@sRq and CreateDate<=@eRq  and Subjectid=245  ),0)
--非营业性收入
set @fyyxsr=isnull((select sum(income) from Finance_RunningAccount_Non where companyid=@companyId and AccountsTypeId=4 and createdate between  @sRq and @eRq),0)+
		ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=2  
and  CreateDate>=@sRq and CreateDate<=@eRq  and AccountType=4  ),0)
--分红
set @fh=isnull((select sum(OutlayPrice) from Finance_Outlay where companyidcur=@companyId and createdate between  @sRq and @eRq
AND Subjectid=196),0)+
ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1  
and  CreateDate>=@sRq and CreateDate<=@eRq  and Subjectid=196  ),0)
--奖金补偿返款
--set @jjbcfk=isnull((select sum(income) from Finance_RunningAccount_Non where companyid=@companyId and AccountsTypeId=6 and createdate between  @sRq and @eRq),0)
--+ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=2  
--and  CreateDate>=@sRq and CreateDate<=@eRq  and AccountType=6  ),0)

/**********************************************************/	
--纯利=毛利+非企QQ渠道-费用
SET @cl=((@IAA+@IAB+@qqqdfqzcml)-@fyze)

--实收纯利 =毛利-费用总额-新增应收+尾款-借款支出+借款收入+非营业性收入+奖金补偿返款
SET @sscl=(@cl-@IAC+@IAF-@jkzc+@jksr+@fyyxsr+@jj+@bcfk)	
/************************2015-01-22**********************************/	

SET @today=1
Select @today=Count(Id) From Statistics_Financial Where CompanyId=@companyId and CreateDate>=@sRq and CreateDate<=@eRq--公司当日数据是否存在
if(@today=0)
begin
	Insert Into Statistics_Financial(
		CompanyId
		,CompanyName
		,OAA
		,OAB
		,OBA
		,OBB
		,OBC
		,OBD
		,OBE
		,OBF
		,OBG
		,OBH
		,OCA
		,OCB,OCC
		,ODA
		,ODB
		,IAA
		,IAB
		,IAC
		,IAD
		,IAE
		,IAF
		,IAG
		,IBD
		,IBDA
		,IBDB
		,IBE
		,IBEA
		,IBEB
		,IBF
		,IBG
		,IBH
		,IBHA
		,IBHB
		,IBI
		,IBIA
		,IBIB
		,IBJ
		,IBK
		,IBL
		,CreateDate,
		OEA,
		OEB,
		OEC,
		OED,
		OEE,
		OEF,
		OEG,
		OEH,
		OEI,
		OEJ,
		OEK,
		OEL,
		[OEM],
		OEN,
		OEO,
		OEP,
		OEQ,
		OER,
		OES,[bd]
		,[wz]
		,[qt]
		,qyQQ,
		idc
		,bd_je,wz_je,qt_je,qyQQ_je,
		idc_je,xzxse,xfxse,
		yggz,sf,rcfy,ff,zbfy,ygfl,sjglf,gdzc,zxf,qtzc,fyze,dkzc,dksr,jkzc,jksr,tzzc,tzsr,ssclwsr,ssclwzc,fyyxsr,fh  
		,cl,sscl ,jj,bcfk,kqfk,qqqdfqzcml,qqqdfqzcxse,bdztc,bdztcxse,QYQQ_CustomerCount)
	Values	(
		@companyId,
		@companyName,
		@OAA,
		@OAB,
		@OBA,
		@OBB,
		@OBC,
		@OBD,
		@OBE,
		@OBF,
		@OBG,
		@OBH,
		@OCA,
		@OCB,
		@OCC,
		@ODA,
		@ODB,
		@IAA,
		@IAB,
		@IAC,
		@IAD,
		@IAE,
		@IAF,
		@IAG,
		@IBD,
		@IBDA,
		@IBDB,
		@IBE,
		@IBEA,
		@IBEB,
		@IBF,
		@IBG,
		@IBH,
		@IBHA,
		@IBHB,
		@IBI,
		@IBIA,
		@IBIB,
		@IBJ,
		@IBK,
		@IBL,
		@CreateDate,
		@OEA,
		@OEB,
		@OEC,
		@OED,
		@OEE,
		@OEF,
		@OEG,
		@OEH,
		@OEI,
		@OEJ,
		@OEK,
		@OEL,
		@OEM,
		@OEN,
		@OEO,
		@OEP,
		@OEQ,
		@OER,
		@OES,
		@bd,
		@wz,
		@qt,
		@qyQQ,
		@idc,
		@bd_je,@wz_je,@qt_je,@qyQQ_je,
		@idc_je,@xzxse,@xfxse,
		@yggz,@sf,@rcfy,@ff,@zbfy,@ygfl,@sjglf,@gdzc,@zxf,@qtzc,@fyze,@dkzc,@dksr,@jkzc,@jksr,@tzzc,@tzsr,@ssclwsr,@ssclwzc,@fyyxsr,@fh            
		,@cl,@sscl,@jj,@bcfk,@kqfk,@qqqdfqzcml,@qqqdfqzcxse,@bdztc,@bdztc_je,@QYQQ_CustomerCount )
end
else
begin
	Update Statistics_Financial Set
		OAA=@OAA,
		OAB=@OAB,
		OBA=@OBA,
		OBB=@OBB,
		OBC=@OBC,
		OBD=@OBD,
		OBE=@OBE,
		OBF=@OBF,
		OBG=@OBG,
		OBH=@OBH,
		OCA=@OCA,
		OCB=@OCB,
		OCC=@OCC,
		ODA=@ODA,
		ODB=@ODB,
		IAA=@IAA,
		IAB=@IAB,
		IAC=@IAC,
		IAD=@IAD,
		IAE=@IAE,
		IAF=@IAF,
		IAG=@IAG,
		IBD=@IBD,
		IBDA=@IBDA,
		IBDB=@IBDB,
		IBE=@IBE,
		IBEA=@IBEA,
		IBEB=@IBEB,
		IBF=@IBF,
		IBG=@IBG,
		IBH=@IBH,
		IBHA=@IBHA,
		IBHB=@IBHB,
		IBI=@IBI,
		IBIA=@IBIA,
		IBIB=@IBIB,
		IBJ=@IBJ,
		IBK=@IBK,
		IBL=@IBL,
		OEA = @OEA,
		OEB = @OEB,
		OEC = @OEC,
		OED = @OED,
		OEE = @OEE,
		OEF = @OEF,
		OEG = @OEG,
		OEH = @OEH,
		OEI = @OEI,
		OEJ = @OEJ,
		OEK = @OEK,
		OEL = @OEL,
		[OEM]= @OEM,
		OEN = @OEN,
		OEO = @OEO,
		OEP = @OEP,
		OEQ = @OEQ,
		OER = @OER,
		OES = @OES,
		[bd]=@bd,
		[wz]=@wz,
		[qt]=@qt,
		qyQQ=@qyQQ,
		idc=@idc,
		bd_je=@bd_je,
		wz_je=@wz_je,
		qt_je=@qt_je,
		qyQQ_je=@qyQQ_je,
		idc_je=@idc_je,
		xzxse=@xzxse,
		xfxse=@xfxse,
		yggz=@yggz,sf=@sf,rcfy=@rcfy,ff=@ff,zbfy=@zbfy,ygfl=@ygfl,
		sjglf=@sjglf,gdzc=@gdzc,zxf=@zxf,qtzc=@qtzc,fyze=@fyze,dkzc=@dkzc,dksr=@dksr,
		jkzc=@jkzc,jksr=@jksr,tzzc=@tzzc,tzsr=@tzsr,ssclwsr=@ssclwsr,ssclwzc=@ssclwzc,fyyxsr=@fyyxsr,fh=@fh ,						
		cl=@cl,sscl=@sscl,jj=@jj,bcfk = @bcfk,kqfk = @kqfk,
		qqqdfqzcml = @qqqdfqzcml,qqqdfqzcxse = @qqqdfqzcxse,
		bdztc=@bdztc,bdztcxse=@bdztc_je,QYQQ_CustomerCount=@QYQQ_CustomerCount
		Where CompanyId=@companyId and CreateDate>=@sRq and CreateDate<=@eRq
end	
--Set @start=@start+1--自增
		--end
End

RETURN